package com.cbw.dao.ImpI;

import com.cbw.bean.User;
import com.cbw.dao.BaseUserDao;
import com.cbw.util.DruidUtil;

import java.sql.*;
import java.util.*;
import java.util.Date;

public class BaseUserDaoMysql implements BaseUserDao {



    private static Connection conn=null;
    private static ResultSet resultSet=null;
    private static PreparedStatement pps=null;

    private static final String SQL_CONSOLE="select "+
            "count(id) total_size,"+  //用户总数
            "count(to_days(createtime)=to_days(now()) or null) day_num "+//今日新增:注册时间=今日时间
            "from euser";
    private static final String SQL_FIND_ALL_BY_LIMIT="select * from euser limit ?,?";//分页查询
    private static final String SQL_FIND_ALL="select * from euser";//查询
    private static final String SQL_FIND_BY_USER_PHONE="select * from euser where userphone=?";//通过用户手机查询
    private static final String SQL_INSERT = "insert into euser(id,number,username,password,userphone,cardid,createtime) values(null,?,?,?,?,?,now())";
    private static final String SQL_UPDATE = "update euser set username=?,userphone=?,cardid=?,password=? where id=?";
    private static final String SQL_DELETE = "delete from euser where id=?";
    private static final String SQL_TOTAL = "select count(*) as total  from euser";

    private static int  userNumber=1000;

    /**
     * 查询数据库的全部用户(总数和新增)、--控制台部分
     *
     * @return {size:总数,day:新增}
     */
    @Override
    public List<Map<String, Integer>> console() {
        ArrayList<Map<String, Integer>> list = new ArrayList<>();
        //获取连接
         conn = DruidUtil.getConnection();
        try {
            //预编译sql
            pps = conn.prepareStatement(SQL_CONSOLE);
            //查询
            resultSet = pps.executeQuery();
            //封装数据
            if (resultSet.next()){
                int total_size=resultSet.getInt("total_size");
                int day_num=resultSet.getInt("day_num");
                HashMap<String, Integer> map = new HashMap<>();
                map.put("total_size",total_size);
                map.put("day_num",day_num);
                list.add(map);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            DruidUtil.close(conn,pps,resultSet);
        }
        return list;
    }

    /**
     * 查询所有用户(区分分页与否)
     *
     * @param limit   是否分页 true为分页
     * @param offset  查询数据的开始索引
     * @param pageNum 每页记录数
     * @return
     */
    @Override
    public List<User> findALl(boolean limit, int offset, int pageNum) {
        //存放单页查询到的数据
        ArrayList<User>list=new ArrayList<>();
        //获取连接
        conn = DruidUtil.getConnection();
        //预编译sql语句
        try {
            if (limit){
                //分页
                pps = conn.prepareStatement(SQL_FIND_ALL_BY_LIMIT);
                //填充参数
                pps.setInt(1,offset);
                pps.setInt(2,pageNum);
            }else {
                //不分页
                pps = conn.prepareStatement(SQL_FIND_ALL);
            }
            //查询
            ResultSet resultSet = pps.executeQuery();
            while (resultSet.next()){
                //封装查询到的数据
                User user = new User();
                user.setId(resultSet.getInt("id"));
                user.setNumber(resultSet.getString("number"));
                user.setUsername(resultSet.getString("username"));
                user.setPassword(resultSet.getString("password"));
                user.setUserPhone(resultSet.getString("userphone"));
                user.setCreateTime(resultSet.getTimestamp("createtime"));
                user.setLastTime(resultSet.getTimestamp("lastime"));
                //封装的数据存入list
                list.add(user);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            DruidUtil.close(conn,pps,resultSet);
        }
        return list;
    }

    /**
     * 通过手机查询用户
     *
     * @param userPhone
     * @return
     */
    @Override
    public User findUserByUserPhone(String userPhone) {
        //获取连接
        conn = DruidUtil.getConnection();
        User user = null;
        try {
            //预编译SQL
            pps = conn.prepareStatement(SQL_FIND_BY_USER_PHONE);
            //填充参数
            pps.setString(1,userPhone);
            //查询
            ResultSet resultSet = pps.executeQuery();

            if (resultSet.next()){
                user = new User();
                user.setId(resultSet.getInt("id"));
                user.setNumber(resultSet.getString("number"));
                user.setUsername(resultSet.getString("username"));
                user.setPassword(resultSet.getString("password"));
                user.setUserPhone(userPhone);
                user.setCardId(resultSet.getString("cardid"));
                user.setCreateTime(resultSet.getTimestamp("createtime"));
                user.setLastTime(resultSet.getTimestamp("lastime"));
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            DruidUtil.close(conn,pps,resultSet);
        }
        return user;
    }

    /**
     * 增加用户
     * insert into euser(id,number,username,password,userphone,cardid,createtime) values(null,?,?,?,?,?,now())
     * lastime从session中获取
     * @param user
     * @return
     */
    @Override
    public boolean insert(User user) {
        //获取连接
        conn = DruidUtil.getConnection();
        try {
            //预编译sql
            pps = conn.prepareStatement(SQL_INSERT);
            //填充参数
            pps.setString(1,userNumber+1+"");
            pps.setString(2,user.getUsername());
            pps.setString(3,user.getPassword());
            pps.setString(4,user.getUserPhone());
            pps.setString(5,user.getCardId());
            //pps.setDate(5, DateFormatUtil.format(lastime));
            //插入
            int i = pps.executeUpdate();
            if (i>0){
                return true;
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            DruidUtil.close(conn,pps,resultSet);
        }
        return false;
    }

    /**
     * 修改用户
     *
     * @param user
     * @return
     */
    @Override
    public boolean update(User user) {
        //获取连接
        conn = DruidUtil.getConnection();
        try {
            //预编译sql
            pps = conn.prepareStatement(SQL_UPDATE);
            //填充参数
            pps.setString(1,user.getUsername());
            pps.setString(2, user.getUserPhone());
            pps.setString(3,user.getCardId());
            pps.setString(4,user.getPassword());
            pps.setInt(5,user.getId());
            //执行更新
            int i = pps.executeUpdate();
            if (i>0) return true;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            DruidUtil.close(conn,pps,resultSet);
        }
        return false;
    }

    /**
     * 删除用户
     *
     * @param id
     * @return
     */
    @Override
    public boolean delete(int id) {
        //获取连接
        conn=DruidUtil.getConnection();
        try {
            //预编译Sql
            pps = conn.prepareStatement(SQL_DELETE);
            //填充参数
            pps.setInt(1,id);
            //删除
            int i = pps.executeUpdate();
            if (i>0) return true;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            DruidUtil.close(conn,pps,resultSet);
        }
        return false;
    }

    @Override
    public int total() {
        //获取连接
        conn = DruidUtil.getConnection();
        try {
            //预编译sql
            pps = conn.prepareStatement(SQL_TOTAL);
            //查询
            ResultSet resultSet = pps.executeQuery();
            if (resultSet.next()){
                int total=resultSet.getInt("total");
                return total;
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            DruidUtil.close(conn,pps,resultSet);
        }
        return 0;
    }
}
